package cn.puppy.postgres.demo.util;

/**
 * @Author: zhanglm
 * @CreateDate: 2024-08-12 14:24
 * @FullNameClass: cn.puppy.postgres.demo.util.MdbUtil
 * @Description:
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
/**
  * @author zlm
 * @since 2021-04-12
 * 读取mdb文件
 */
public class MdbUtil {

    /**
     *
     * @param mdbPath mdb文件路径
     * @param mdbSql mdb执行sql
     * @param mdbColumnList mdb查询字段
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> resolverMdb(String mdbPath, String mdbSql, List<String> mdbColumnList) throws Exception {
        if (mdbPath.isEmpty() || mdbSql.isEmpty() || mdbColumnList.isEmpty()) {
            throw new Exception("mdb文件路径不能为空或者SQL语句不能为空或者返回字段列表不能为空");
        }
        List<Map<String, Object>> mdbEntityList = new ArrayList<Map<String, Object>>();
        Properties prop = new Properties();
        //设置编码
        prop.put("charSet", "UTF-8");
        prop.put("password","GYFX");
        //数据地址
        String dbUrl = "jdbc:ucanaccess://" + mdbPath;
        //引入驱动
        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver").newInstance();
        try {
            //连接数据库资源
            Connection conn = DriverManager.getConnection(dbUrl, prop);
            //建立查询事务
            Statement statement = conn.createStatement();
            //执行查询
            ResultSet result = statement.executeQuery(mdbSql);
            //解析执行结果
            Map<String, Object> mdbMapList = new HashMap<String, Object>(16);
            while (result.next()) {
                for (String col : mdbColumnList) {
                    mdbMapList.put(col, result.getObject(col));
                }
                mdbEntityList.add(mdbMapList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //返回数据
        return mdbEntityList;
    }

//    public static void main(String[] args) throws Exception {
//        String mdbPath = "E://gf.mdb";
//        String mdbSql = "SELECT * FROM result";
//        List<String> mdbList = new ArrayList<String>();
//        mdbList.add("试验编号");
//        mdbList.add("试样名称");//对应manufact_contract_sample.sample_code
//        mdbList.add("水分含量");
//        mdbList.add("空干基灰分");
//        mdbList.add("干基灰分");//干基灰分》》》Ad
//        mdbList.add("空干基挥发份");
//        mdbList.add("干基挥发份");
//        mdbList.add("干燥无灰基挥发份");//干燥无灰基挥发份》》》vdaf
//        mdbList.add("空干基固定碳");
//        mdbList.add("干基固定碳");
//        mdbList.add("测试日期");
//        mdbList.add("灰渣可燃物");
//        mdbList.add("化验员");
//        //根据“试样名称”去查找 对应manufact_contract_sample.sample_code：
//        //如存在对应的样本号，再查询“五灰基分”是否与manufact_item表中的name对应
//        //如对应将对应的值 放到test_value字段中
//        //将manufact_item的status/progress字段值改为37
//        List<Map<String, Object>> list = MdbUtil.resolverMdb(mdbPath, mdbSql, mdbList);
//        System.out.println(JSON.toJSON(list));
//    }

}
